package com.saic.parse.impl;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import com.saic.parse.IImport;



public class ImportBrand implements IImport {

	@Override
	public void importExcel(Connection conn) throws Exception {
		Statement stmt = conn.createStatement();
		stmt.execute("drop table if EXISTS `t_config_temp`;");
		stmt.execute("CREATE TABLE `t_config_temp` (`id` int(11) NOT NULL ,`web_id` varchar(20), `name` varchar(60),`value` varchar(60) )");
		stmt.execute("delete from t_brand;");
		stmt.execute("delete from t_subbrand;");
		stmt.execute("delete from t_velseries ;");
		stmt.execute("delete from t_velmodel;");
		stmt.execute("ALTER TABLE t_brand AUTO_INCREMENT = 1;");
		stmt.execute("ALTER TABLE t_subbrand AUTO_INCREMENT = 1000;");
		stmt.execute("ALTER TABLE t_velseries AUTO_INCREMENT = 10000;");
		stmt.execute("ALTER TABLE t_velmodel AUTO_INCREMENT = 100000;");
		stmt.execute("delete from t_brand");
		InputStream stream = new FileInputStream("d:/brand_contrast.xls");  
		HSSFWorkbook wb = new HSSFWorkbook(stream);
		Sheet sheet = wb.getSheetAt(0);  
		for(Row row:sheet){
			String name = null;
			String desc = null;
			String xcarid = null;
			String autohomeid = null;
			String bitautoid = null;
			String url = null;
			for(Cell cell:row){
				//主品牌名
				if(cell.getColumnIndex()==0){
					name = cell.toString();
				}
				//来源
				if(cell.getColumnIndex()==1){
					desc = cell.toString();
				}
				//xcarid
				if(cell.getColumnIndex()==2){
					xcarid = cell.toString();
				}
				//autohomeid
				if(cell.getColumnIndex()==3){
					autohomeid = cell.toString();
				}
				//bitautoid
				if(cell.getColumnIndex()==4){
					bitautoid = cell.toString();
				}
				//url
				if(cell.getColumnIndex()==5){
					url = cell.toString();
				}
			}
			stmt.execute("insert into t_brand(`name`,`desc`,xcardid,autoHomeID,bitautoId,status,imageUrl) values('" +name+
					"','" +desc+
					"','" +xcarid+
					"','" +autohomeid+
					"','" +bitautoid+
					"',0,'" +url+
					"')");

		}
			
	}
	
	public static void main(String[] args) {
		System.out.println(System.getProperty("user.dir"));
	}
}
